﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DBLayer
{
    public class DAL
    {
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private SqlDataAdapter dataAdapter = null;
        private SqlTransaction trans = null;

        public DAL()
        {
            Configuration configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            String dataSource = configuration.AppSettings.Settings["dataSource"].Value;
            String initialCatalog = configuration.AppSettings.Settings["initialCatalog"].Value;
            
            String connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + initialCatalog + ";Integrated Security=True";
            conn = new SqlConnection(connectionString);
            cmd = conn.CreateCommand();
        }

        public DataSet ExecuteQueryDataSet(String queryString,CommandType ct,params SqlParameter[] param)
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();

            conn.Open();

            cmd.CommandType = ct;
            cmd.CommandText = queryString;

            cmd.Parameters.Clear();
            if(param != null)
            {
                foreach (SqlParameter p in param)
                    cmd.Parameters.Add(p);
            }

            dataAdapter = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);
            
            dataAdapter.Dispose();
            dataAdapter = null;

            return ds;
        }

        public bool ExecuteNonQuery(String strSQL,CommandType ct,ref string error,params SqlParameter[] param)
        {
            bool f = false;

            cmd.Parameters.Clear();
            cmd.CommandText = strSQL;
            cmd.CommandType = ct;

            if(param != null)
            {
                foreach (SqlParameter p in param)
                    cmd.Parameters.Add(p);
            }

            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                cmd.ExecuteNonQuery();
                f = true;
            }
            catch (SqlException ex)
            {
                error = ex.Message;
            }

            return f;
        }

        public void BeginTransaction()
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            trans = conn.BeginTransaction();
            cmd.Transaction = trans;
        }

        public void CommitTransaction()
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            trans.Commit();
            cmd.Transaction = null;
        }

        public void RollbackTransaction()
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            trans.Rollback();
            cmd.Transaction = null;
        }
    }
}
